#encoding: utf-8

=begin
处理和修正各中bug或者修理记录专用库
每次处理后可备注
=end

load 'tutem_utils.rb'

#检查是否有oid/cart_id重复的订单
def check_duplicated_ocids
    db = 'tutem_mall'
    #@rds = @rds_fc
    #db = 'crmeb'

    sql = "select oid, count(*) as cnt from #{db}.eb_store_order_cart_info group by oid order by cnt desc"
    res = queryRds sql
    oids = []
    res.each do |r|
        break if r['cnt'] < 2
        oids += [r['oid']]
    end

    repeated = []

    if oids.size > 0

        oids_str = oids.join(',')
        sql = "select oid, cart_id, product_id, `unique` from #{db}.eb_store_order_cart_info where oid in (#{oids_str})"
        recs = {}
        res = queryRds sql
        res.each do |r|
            oid = r['oid']
            cart_id = r['cart_id']
            if recs[oid].nil?
                recs.store oid, cart_id
            else
                repeated += [oid] if recs[oid] == cart_id
            end
        end
        repeated = repeated.uniq

    end

    return repeated

end
=begin

ap check_duplicated_ocids

=end

#处理tupal11的重复记录值
def remove_duplicated_tupal11_sqls p1, p2

    userEnrollCounter = {}
    userFirstEnroll = {}
    userSecondEnroll = {}
    sql = "select * #{p1}"
    (queryRds(sql)).each do |q|
        uid = q['uid']
        userEnrollCounter.store uid, 0 if userEnrollCounter[uid].nil?
        userEnrollCounter.store uid, userEnrollCounter[uid] + 1
        userFirstEnroll.store uid, q if userEnrollCounter[uid] == 1
        userSecondEnroll.store uid, q if userEnrollCounter[uid] == 2
    end

    sqls = []
    userSecondEnroll.keys.each do |uid|
        firstEnroll = userFirstEnroll[uid]
        secondEnroll = userSecondEnroll[uid]
        matched = firstEnroll['status'] == secondEnroll['status']
        if matched
            seid = secondEnroll['id']
            feid = firstEnroll['id']
            sqls += ["delete #{p2} id = #{seid} and uid = #{uid}"]
        else
            puts "not matched: feid##{feid} seid#{seid}"
        end
    end

    #commitTrans sqls
    return sqls

end

def squeeze_new_tupal11_enroll
    p1 = "from tutem_mall.eb_tupal_enroll where program_id = 3"
    p2 = "from tutem_mall.eb_tupal_enroll where program_id = 3 and "
    sqls = remove_duplicated_tupal11_sqls p1, p2
end

def squeeze_old_tupal11_enroll
    p1 = "from tutem_mall.eb_tupal where uid > 0"
    p2 = "from tutem_mall.eb_tupal where "
    sqls = remove_duplicated_tupal11_sqls p1, p2
end

=begin
#2022-10-11 处理由于快速点击创建多条图腾之友注册记录导致的重复

commitTrans squeeze_new_tupal11_enroll
commitTrans squeeze_old_tupal11_enroll

=end

#2022-10-13 由于某种不知道的原因，webhook收录了问卷数据之后，在enroll中竟然没有创建记录，因此需要给他们生产数据
def enroll_leaked_surveys program_id

    programInfo = {}
    sql = "select * from tutem_mall.eb_tupal_program"
    (queryRds(sql)).each do |q|
        programInfo.store q['id'], q
    end

    #用group by uid的原因是如果有多条answer.create只取第一条
    sql = " SELECT s.*, u.real_name as user_name, l.program_id, l.program_code, l.survey_hash
        FROM
            tutem_mall.eb_tupal_survey s
            LEFT JOIN tutem_mall.eb_user u ON ( s.uid = u.uid )
            LEFT JOIN tutem_mall.eb_survey_list l ON (s.survey_id = l.survey_id)
        WHERE
            s.survey_id IN ( SELECT survey_id FROM tutem_mall.eb_survey_list WHERE program_id = 3 )
            AND s.action = 'answer.create'
            AND s.uid NOT IN ( SELECT uid FROM tutem_mall.eb_tupal_enroll WHERE program_id = 3 )
        GROUP BY
            s.uid;"

    createSqls = []
    updateSqls = []
    botMessages = []
    (queryRds(sql)).each do |q|

        uid = q['uid']
        program_id = q['program_id']
        description = programInfo[program_id]['description'].to_s
        program_code = programInfo[program_id]['code'].to_s
        payload = q['payload']
        user_name = q['user_name']
        name = q['user_name'] + '的' + program_code + '计划申请表'
        status = 'DRAFT'
        answers = '{}'
        summary = '等待用户填写'
        add_time = q['add_time']
        submit_time = 0
        close_time = 0
        comment = ''
        history = "[#{Time.at(add_time).strftime('%Y-%m-%d %H:%M:%S')}] 系统补生成登记申请表\n"
        survey_id = q['survey_id'].to_s
        survey_hash = q['survey_hash'].to_s

        csql = "insert into tutem_mall.eb_tupal_enroll(
                uid,program_id,name,status,
                description,answers,summary,
                add_time,submit_time,close_time,
                comment,history,survey_id,survey_hash
            ) values (
                #{uid}, #{program_id}, '#{name}', '#{@rds.escape status}',
                '#{@rds.escape description}', '#{@rds.escape answers}', '#{@rds.escape summary}',
                add_time, submit_time, close_time,
                '#{@rds.escape comment}', '#{@rds.escape history}', survey_id, survey_hash
            );"

        botMessages += ["系统补登了用户##{uid} #{user_name}于#{Time.at(add_time).strftime('%Y-%m-%d %H:%M:%S')}提交的#{program_code}调查表，答案详情：#{answers_url}"]

    end

    commitTrans createSqls
    commitTrans updateSqls
    #botMessages.each { |msg| send_message_to_bot 'WWBOT_XT', msg, ['@all'] }

end
##2022-10-13 10:51运行
#enroll_leaked_surveys 3

#2022-10-13 有些用户生成了DRAFT，且提交了问卷之后，状态没有变成DRAFT
def draft_surveys_to_submitted program_id

    programInfo = {}
    sql = "select * from tutem_mall.eb_tupal_program"
    (queryRds(sql)).each do |q|
        programInfo.store q['id'], q
    end

    #用group by uid的原因是如果有多条answer.create只取第一条
    sql = " SELECT s.*, u.real_name as user_name, l.program_id, l.program_code, l.survey_hash
        FROM
            tutem_mall.eb_tupal_survey s
            LEFT JOIN tutem_mall.eb_user u ON ( s.uid = u.uid )
            LEFT JOIN tutem_mall.eb_survey_list l ON (s.survey_id = l.survey_id)
        WHERE
            s.survey_id IN ( SELECT survey_id FROM tutem_mall.eb_survey_list WHERE program_id = 3 )
            AND s.action = 'answer.create'
            AND s.uid IN ( SELECT uid FROM tutem_mall.eb_tupal_enroll WHERE program_id = 3 and status = 'DRAFT')
        GROUP BY
            s.uid;"

    updateSqls = []
    botMessages = []
    (queryRds(sql)).each do |q|

        uid = q['uid']
        program_id = q['program_id']
        description = programInfo[program_id]['description'].to_s
        program_code = programInfo[program_id]['code'].to_s
        payload = q['payload']
        user_name = q['user_name']
        name = q['user_name'] + '的' + program_code + '计划申请表'
        status = 'DRAFT'
        answers = '{}'
        summary = '等待用户填写'
        add_time = q['add_time']
        submit_time = 0
        close_time = 0
        comment = ''
        history = "[#{Time.at(add_time).strftime('%Y-%m-%d %H:%M:%S')}] 系统补生成登记申请表\n"
        survey_id = q['survey_id'].to_s
        survey_hash = q['survey_hash'].to_s

        #接着将现有的数据复制上去
        his_add = "[#{Time.at(add_time).strftime('%Y-%m-%d %H:%M:%S')}] 用户已提交计划注册，等待审批\n"
        pl = JSON.parse payload
        answers_url = "https://wj.qq.com/survey_detail.html?sid=#{survey_id}&aid=#{pl['answer_id'].to_s}&user_type=editor"
        answers = "答案详情：#{answers_url}" #TBO
        summary = '用户信息已提交，审批或处理反馈将通过图腾生命医学公众号下发'
        sql = "update tutem_mall.eb_tupal_enroll set payload = '#{@rds.escape payload}', history = concat('#{his_add}', history), answers = '#{@rds.escape answers}', submit_time = #{add_time}, status = 'SUBMITTED', summary = '#{@rds.escape summary}' where uid = #{uid} and program_id = #{program_id}"
        updateSqls += [sql]

    end

    #ap updateSqls
    commitTrans updateSqls

end
##2022-10-13 11:37运行
#draft_surveys_to_submitted 3
